﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Data.OleDb;
using System.IO;
using System.Net;
using System.Xml;
using Com.Toook.Common;
using Com.Toook.ParseHtml;
using Com.Toook.ParseHtml.Entity;
using System.Text.RegularExpressions;

namespace Com.Toook.UpdateRssNews
{
    /// <summary>
    /// UpdateRssNews 的摘要说明
    /// </summary>
    public class UpdateRssNewsAccess
    {
        public UpdateRssNewsAccess()
        {
            // TODO: 在此处添加构造函数逻辑
            //this.connection = ConfigurationManager.ConnectionStrings["RssNewsString"].ConnectionString
            //    + HttpContext.Current.Server.MapPath("~/App_Data/rss.mdb");
            //this.connection = //GetConfigContent.ConnectionStringNews;
            //    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            //    HttpContext.Current.Server.MapPath("~/App_Data/rss.mdb");
        }
        //private string connection;

        public void UpdateAllSiteNews()
        {
            int sid = 0, showCount = 0, timeSpan = 60;
            string url = string.Empty;
            DateTime expiryTime = DateTime.Now;
            string newsType = "";
            string xmlFileName = "";
            string xmlSiteId = "";
            int haveContent = 0;

            string command = "select top 1 SiteId, Url, NewsCount, ExpiryTime, TimeSpan, haveContent, newsType, xmlFileName, xmlSiteId " +
                " from RssSites where IsValid=1 order by ExpiryTime asc";
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand(command, con);
            OleDbDataReader reader = null;
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    sid = int.Parse(reader[0].ToString());
                    url = reader[1].ToString();
                    showCount = int.Parse(reader["NewsCount"].ToString());
                    DateTime.TryParse(reader["ExpiryTime"].ToString(), out expiryTime);
                    int.TryParse(reader[4].ToString(), out timeSpan);
                    int.TryParse(reader["haveContent"].ToString(), out haveContent);
                    newsType = reader["newsType"].ToString();
                    xmlFileName = reader["xmlFileName"].ToString();
                    xmlSiteId = reader["xmlSiteId"].ToString();
                }
            }
            catch(Exception ex)
            {
                LogHelper.Log.Error("UpdateAllSiteNews() 读取RssSites数据错误", ex);
            }
            finally
            {
                AccessHelper.CloseDbResource(con, reader);
            }            

            try
            {
                if(expiryTime <= DateTime.Now)
                {
                    DownloadXml(sid, url, showCount, timeSpan, haveContent, newsType, xmlFileName, xmlSiteId);//, connection
                }
            }
            catch(Exception xmlDownEx)
            {
                string error = xmlDownEx.Message;
                error = error.Replace('\'', ' ');
                string errorCommand = "insert into errors(SiteId,Description,ErrorTime,Url) values(" + sid.ToString() + ",'" + error + "','" + DateTime.Now.ToString() + "','" + url + "')";
                LogHelper.Log.Error("下载Rss xml 错误。" + errorCommand, xmlDownEx);
                cmd.CommandText = errorCommand;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                UpdateExpiryTime(sid, timeSpan * 2);////下载或插入出错时，更新时间，加2小时。 , connection
            }
        }
        /// <summary>
        /// 手动更新单个站点的新闻
        /// </summary>
        /// <param name="sid">站点ID</param>
        /// <param name="url">站点地址</param>
        /// <param name="showCount">显示新闻的条数</param>
        /// <param name="timeSpanMinute">增加的时间间隔</param>
        /// <returns>返回值，正常为ok出错为异常原因</returns>
        public string UpdateOneSite(int sid)
        {
            /*
            string result = "ok";
            try
            {
                DownloadXml(sid, url, showCount, timeSpanMinute, "无");
            }
            catch (Exception e)
            {
                result = e.Message;
            }
            return result;
            */
                        
            string result = "ok";
            string command = "select Url, NewsCount, ExpiryTime, TimeSpan, haveContent, newsType, xmlFileName, xmlSiteId " +
                " from RssSites where SiteId=" + sid.ToString();
            string url = "";
            int showCount = 40;
            DateTime expiryTime = DateTime.Now;
            int timeSpanMinute = 60;
            int haveContent = 0;            
            string xmlFileName = "";
            string xmlSiteId = "";
            string newsType = "";
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand(command, con);
            OleDbDataReader reader = null;
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    url = reader["Url"].ToString();
                    showCount = int.Parse(reader["NewsCount"].ToString());
                    DateTime.TryParse(reader["ExpiryTime"].ToString(), out expiryTime);
                    int.TryParse(reader["TimeSpan"].ToString(), out timeSpanMinute);
                    int.TryParse(reader["haveContent"].ToString(), out haveContent);
                    newsType = reader["newsType"].ToString();
                    xmlFileName = reader["xmlFileName"].ToString();
                    xmlSiteId = reader["xmlSiteId"].ToString();
                }
                else
                {
                    return "此SiteId没有对应的记录";
                }
            }
            finally
            {
                AccessHelper.CloseDbResource(con, reader);
            }
            try
            {
                DownloadXml(sid, url, showCount, timeSpanMinute, haveContent, newsType, xmlFileName, xmlSiteId);
                //string filePath = System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "App_Data\\RssNewsContent\\" + xmlFileName;
                //GetNewsContent(filePath, xmlSiteId, sid);
            }
            catch (Exception e)
            {
                result = e.Message;
            }
            return result;            
        }
        /// <summary>
        /// 获取RSS中的记录，并获取内容后，保存到数据库
        /// </summary>
        /// <param name="sid">站点id</param>
        /// <param name="url">Rss URL</param>
        /// <param name="showCount">数据库最多保存此站点多少条数据</param>
        /// <param name="timeSpan">更新时间间隔</param>
        /// <param name="haveContent">是否成功获取内容</param>
        /// <param name="newsType">新闻类型</param>
        /// <param name="xmlFileName">RssSites表中的xmlFileName</param>
        /// <param name="xmlSiteId">RssSites表中的xmlSiteId</param>
        protected void DownloadXml(int sid, string url, int showCount, int timeSpan, int haveContent, string newsType, string xmlFileName, string xmlSiteId)
        {
            byte[] rssXml = null;
            try
            {
                rssXml = new WebClient().DownloadData(url);
            }
            catch (Exception de)
            {
                LogHelper.Log.Warn("下载失败。WebClient().DownloadData(" + url + ")", de);
                UpdateExpiryTime(sid, timeSpan);
                return;
            }
            XmlDocument doc = new XmlDocument();
            doc.Load(new MemoryStream(rssXml));
            XmlNodeList items = doc.SelectNodes("rss/channel/item");
            
            int sameCount = 0, insertCount = showCount;
            //取得RssNews中最大的InsertNumber值，即最后插入的值connection
            int maxNumber = GetMaxInsertNumber(sid);
            
            foreach(XmlNode item in items)
            {
                News news;
                if (!IsNewsValid(item, out news, sid, url, newsType, xmlFileName, xmlSiteId, haveContent))
                {
                    continue;//新闻数据不合法
                }
                string haveCmd = "SELECT count(link) AS linkCount FROM RssNews WHERE siteId=" + sid.ToString() + " and link=?";
                OleDbConnection conhave = AccessHelper.GetConnection();
                OleDbCommand cmdhave = new OleDbCommand(haveCmd, conhave);
                cmdhave.Parameters.Add("link", OleDbType.VarChar, 200).Value = news.Link;
                int counter = 1;
                try
                {
                    conhave.Open();
                    counter = (int)cmdhave.ExecuteScalar();
                }
                finally
                {
                    conhave.Close();
                }
                if(counter > 0)
                {
                    if(++sameCount > 3) //如果连续三个都已经有，跳出循环，更新站点时间。
                        break;
                    else
                        continue;
                }
                else
                {
                    //xml第一条是最新的记录,所以要insertCount--从大到小插入。
                    int num = (maxNumber + insertCount--);
                    sameCount = 0;//有新的，将 sameCount 重置为零

                    string sql = "";
                    int updateId = GetUpdateId(sid, showCount);
                    if (updateId == 0)
                    {
                        sql = "insert into RssNews(title,link,pubDate,siteId,haveContent, insertNumber, newsType, content) " +//
                            " values(?, ?, ?, '" + sid.ToString() + "'," + news.HaveContent.ToString() + ","  + num.ToString() + "," + newsType + ", ?)";//
                    }
                    else
                    {
                        sql = "update RssNews set title=?,link=?,pubDate=?,siteId=" + sid.ToString() + ",insertNumber=" + num.ToString() + ",newsType=" + newsType +
                            ",haveContent=" + news.HaveContent.ToString() + ", content=? , createTime=? " +  " where id=" + updateId.ToString();
                    }

                    OleDbConnection con = AccessHelper.GetConnection();
                    OleDbCommand com = new OleDbCommand(sql, con);
                    com.Parameters.Add("title", OleDbType.VarWChar, 40).Value = news.Title;
                    com.Parameters.Add("link", OleDbType.VarChar, 200).Value = news.Link;
                    com.Parameters.Add("pubDate", OleDbType.VarChar, 20).Value = news.PubDate;
                    com.Parameters.Add("content", OleDbType.LongVarWChar).Value = news.Content;
                    com.Parameters.Add("createTime", OleDbType.Date).Value = DateTime.Now;

                    try
                    {
                        con.Open();
                        com.ExecuteNonQuery();                        
                    }
                    catch(Exception exx)
                    {
                        insertCount++;
                        LogHelper.Log.Error("插入新闻失败." + news.ToString(), exx);
                    }
                    finally
                    {
                        con.Close();
                    }
                    if(insertCount == 0)//加够 showCount 条后，跳出。
                    {
                        break;
                    }
                }
            }//end foreach

            //int RssNewsCount = GetRssNewsCount(sid); 
            //while(RssNewsCount > showCount)
            //{
            //    try
            //    {
            //        DeleteTop(sid);  //delete top one news,which is not ad , connection
            //    }
            //    catch(Exception deleteEx)
            //    {
            //        string error = deleteEx.Message;
            //        error = error.Replace('\'', ' ');
            //        string errorCommand = "insert into errors(SiteId,Description,ErrorTime,Url) values(" + sid.ToString() + ",'我的程序删除数据出错，原因：" + error + "','" + DateTime.Now.ToString() + "','" + url + "')";
            //        OleDbConnection conInsert = AccessHelper.GetConnection();
            //        OleDbCommand cmdInsert = new OleDbCommand(errorCommand, conInsert);//
            //        cmdInsert.CommandText = errorCommand;
            //        conInsert.Open();
            //        cmdInsert.ExecuteNonQuery();
            //        conInsert.Close();
            //    }
            //    RssNewsCount--;

            //}
            UpdateExpiryTime(sid, timeSpan);//, connection
        }
        /// <summary>
        /// 解析出新闻数据，并判断数据是否合法
        /// </summary>
        /// <param name="nodeNews"></param>
        /// <param name="sid"></param>
        /// <param name="url"></param>
        /// <returns></returns>
        protected bool IsNewsValid(XmlNode nodeNews, out News news, int sid, string url, string newsType, string xmlFileName, string xmlSiteId, int haveContent)
        {
            news = new News();
            //大洋网没有link,是guid
            XmlNode linkNode = nodeNews.SelectSingleNode("link") != null ? nodeNews.SelectSingleNode("link") : nodeNews.SelectSingleNode("guid");
            XmlNode titleNode = nodeNews.SelectSingleNode("title");
            XmlNode dateNode = nodeNews.SelectSingleNode("pubDate");
            XmlNode descNode = nodeNews.SelectSingleNode("description");
            
            if (linkNode == null || titleNode == null)
            {
                LogHelper.Log.Error("RSS linkNode==null = " + (linkNode == null).ToString() + ", titleNode==null = " + (titleNode == null).ToString() + ", " + sid.ToString() + ", url=" + url);
                return false;
            }

            string link = linkNode.InnerText;
            if (!link.StartsWith("http"))
            {
                LogHelper.Log.Error("link地址错误, link=" + link + ", " + sid.ToString() + ", url=" + url);
                return false;
            }

            //新华网的日期不在pubDate中
            string pubDate = "";
            if (dateNode != null)
            {
                pubDate = ParsePubDate(dateNode.InnerText, url);
            }
            else
            {
                if (url.Contains("xinhuanet"))
                {
                    //1、可以这样 pubDate = ParsePubDate(item.ChildNodes[3].Value, url);
                    //2、也可以：
                    XmlNodeList nodes = nodeNews.ChildNodes;
                    foreach (XmlNode node in nodes)
                    {
                        if (node.NodeType == XmlNodeType.Text)
                        {
                            pubDate = ParsePubDate(node.Value, url);
                            break;
                        }
                    }
                }
                else
                {
                    pubDate = DateTime.Now.ToString(ConfigurationManager.AppSettings["NewsPubDateFormat"]);
                    LogHelper.Log.Warn("没有日期节点。sid=" + sid.ToString() + ", url=" + url);
                }
            }
            string content;
            if (haveContent == 1 && descNode != null)
            {
                content = descNode.InnerText;
            }
            else
            {
                content = GetNewsContent(link, xmlFileName, xmlSiteId);
            }
            //content = Regex.Replace(content, @"<script\b([^>]+)>.*?</script>", "", RegexOptions.IgnoreCase);//删除脚本
            news.Content = Regex.Replace(content, @"<!--\s?\b[^>]+-->", "", RegexOptions.IgnoreCase);//删除注释 <!-- 注释 -->
            news.HaveContent = string.IsNullOrEmpty(news.Content) ? 0 : 1;
            news.InsertNumber = 0;
            news.Link = link.Length > 200 ? link.Substring(0, 200) : link;
            news.PubDate = pubDate;
            news.SiteId = sid;
            string title = titleNode.InnerText.Replace("&amp;", "&").Replace("&nbsp;", " ").Replace("nbsp;", "");
            news.Title = HttpUtility.HtmlDecode(title);
            if (news.Title.Length > 40)
            {
                news.Title = news.Title.Substring(0, 40);
            }
            news.NewsType = newsType;

            return true;
        }

        /// <summary>
        /// 获取内容
        /// </summary>
        /// <param name="xmlFileName">RssSites表中的xmlFileName</param>
        /// <param name="xmlSiteId">xml中的siteId 如 sina_world</param>
        /// <param name="siteId">RssSites中的分类SiteId 如 101</param>
        protected string GetNewsContent(string link, string xmlFileName, string xmlSiteId)
        {
            string xmlFilePath = System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "App_Data\\RssNewsContent\\" + xmlFileName;
            CreateContentByXml createContent = new CreateContentByXml(xmlFilePath);
            DetailRoadMap roadMap = createContent.GetDetailRoadMap(xmlSiteId);
            GetContentResultFromHtml html = new GetContentResultFromHtml(roadMap);

            return html.GetNewsContent(link);
        }
        /// <summary>
        /// 获取RssNews表中SiteId 对应的记录id(不足100条记录时插入，有100条时更新最早的一条)
        /// </summary>
        /// <param name="siteId"></param>
        /// <param name="maxCount"></param>
        /// <returns>1、返回0表示此分类记录没有达到最大，直接插入数据  2、返回大于0的数表示Id,更新此id的记录</returns>
        protected int GetUpdateId(int siteId, int maxCount)
        {
            int resultId = 0;
            string sqlCount = "select count(*) from RssNews where siteId=" + siteId.ToString();//查询分类记录总数
            string sqlId = "select top 1 id from RssNews where siteId=" + siteId.ToString() + " order by insertNumber asc";//createTime asc查询分类记录中最早添加的id
            OleDbConnection con =AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand(sqlCount, con);
            
            int number = 0;
            try
            {
                con.Open();
                int.TryParse(cmd.ExecuteScalar().ToString(), out number);
                if (number >= maxCount)//如果等于/超过maxCount，返回最早添加的id,供循环更新
                {
                    cmd.CommandText = sqlId;
                    object id = cmd.ExecuteScalar();
                    if (id != null)
                    {
                        int.TryParse(id.ToString(), out resultId);
                    }
                }
                else//如果刚开始采集，没有达到最大条数时，返回0，直接插入数据
                {
                    resultId = 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("获取RssNews 表中最早更新id/分类记录数失败。siteId=" + siteId, ex);
            }
            finally
            {
                con.Close();
            }

            return resultId;
        }

        /// <summary>
        /// 理RSS源的日期
        /// </summary>
        /// <param name="pubDate">RSS的日期</param>
        /// <returns>月日</returns>
        protected string ParsePubDate(string pubDate, string url)
        {
            string result;
            DateTime date = DateTime.Now;
            if (!DateTime.TryParse(pubDate, out date))
            {
                LogHelper.Log.Warn("解析日期出错。pubDate=" + pubDate + ", Rss地址=" + url);
            }
            result = date.ToString(ConfigurationManager.AppSettings["NewsPubDateFormat"]);//输出配置文件中指定的日期格式。如："yyyy年MM月dd日"
            return result;
        }
        protected void DeleteTop(int sid)
        {
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand("DeleteMin", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("siteId", OleDbType.Integer, 4).Value = sid;
            try
            {
                con.Open();
                cmd.ExecuteScalar();
            }
            finally
            {
                con.Close();
            }
        }
        protected int GetRssNewsCount(int sid)
        {
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand("GetRssNewsCount", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("siteId", OleDbType.Integer, 4).Value = sid;
            int number = 0;
            try
            {
                con.Open();
                number = (int)cmd.ExecuteScalar();
            }
            finally
            {
                con.Close();
            }
            return number;
        }
        /// <summary>
        /// 获取RssNews表中最大的InsertNumber,即最后插入的值
        /// </summary>
        /// <returns></returns>
        protected int GetMaxInsertNumber(int siteId)
        {
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand("GetMaxInsertNumber", con);
            cmd.Parameters.Add("siteId", OleDbType.Integer).Value = siteId;
            cmd.CommandType = CommandType.StoredProcedure;
            int number = 0;
            try
            {
                con.Open();
                object num = cmd.ExecuteScalar();
                
                if(num != null && !string.IsNullOrEmpty(num.ToString()))
                {
                    int.TryParse(num.ToString(), out number);
                }
            }
            finally
            {
                con.Close();
            }            
            
            return number;
        }

        protected void UpdateExpiryTime(int sid, int timeSpan)//, string connection
        {
            OleDbConnection con = AccessHelper.GetConnection();
            string command = "update RssSites set ExpiryTime='" + DateTime.Now.AddMinutes(timeSpan) + "' where SiteId=" + sid.ToString();
            OleDbCommand cmd = new OleDbCommand(command, con);
            try
            {
                con.Open();
                int count = cmd.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }            
        }
    }
}